﻿using DAL;
using Model;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace MainOP
{
    public partial class FormDataSelectLeak : Form
    {
        public FormDataSelectLeak()
        {
            InitializeComponent();
        }
        List<ProductData> objProductData = new List<ProductData>();
        private void button1_Click(object sender, EventArgs e)
        {
            //查询前先清空数据集
            objProductData.Clear();
            //清空表格再查询
            while (this.dataGridView1.Rows.Count != 1)
            {
                this.dataGridView1.DataSource = null;
            }
            string sql = "select [产品下线时间], [产品型号], [总成码],[内部码],[ID],[托盘号],[OP70操作时间],[总结果], [OP70结果], [OP70暖芯正压值], [OP70暖芯负压值], [OP70蒸发器负压值] from productData where [产品下线时间] Between '{0}' and '{1}' order by [产品下线时间] ASC";
            sql = string.Format(sql, dateTimePicker1.Text, dateTimePicker2.Text);
            SqlDataReader objReader = SQLHelp.GetReader(sql);
            //循环写入数据
            while (objReader.Read())
            {
                ProductData productData = new ProductData();
                try
                {
                    productData.产品下线时间 = Convert.ToDateTime(objReader["产品下线时间"]);
                }
                catch
                { }
                productData.产品型号 = objReader["产品型号"].ToString();
                productData.总成码 = objReader["总成码"].ToString();
                productData.ID = objReader["ID"].ToString();
                productData.内部码 = objReader["内部码"].ToString();
                productData.总结果 = objReader["总结果"].ToString();
                productData.OP70结果 = objReader["OP70结果"].ToString();
                productData.OP70暖芯正压值 = objReader["OP70暖芯正压值"].ToString();
                productData.OP70暖芯负压值 = objReader["OP70暖芯负压值"].ToString();
                productData.OP70蒸发器负压值 = objReader["OP70蒸发器负压值"].ToString();
                productData.托盘号 = objReader["托盘号"].ToString();
                productData.OP70操作时间 = objReader["OP70操作时间"].ToString();
                objProductData.Add(productData);
            }
                dataGridView1.AutoGenerateColumns = false;
                dataGridView1.DataSource = objProductData;
            
            objReader.Close();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            //查询前先清空数据集
            objProductData.Clear();
            //清空表格再查询
            while (this.dataGridView1.Rows.Count != 1)
            {
                this.dataGridView1.DataSource = null;
            }
            string sql = "select * from productData where concat([产品下线时间], [产品型号], [总成码],[ID], [总结果], [OP70结果], [OP70暖芯正压值], [OP70暖芯负压值], [OP70蒸发器负压值], [托盘号], [OP70操作时间]) like '%{0}%'";
            sql = string.Format(sql, textBox1.Text);
            SqlDataReader objReader = SQLHelp.GetReader(sql);
            //循环写入数据
            while (objReader.Read())
            {
                ProductData productData = new ProductData();
                try
                {
                    productData.产品下线时间 = Convert.ToDateTime(objReader["产品下线时间"]);
                }
                catch
                {

                }
                productData.产品型号 = objReader["产品型号"].ToString();
                productData.ID = objReader["ID"].ToString();
                productData.总成码 = objReader["总成码"].ToString();
                productData.总结果 = objReader["总结果"].ToString();
                productData.OP70结果 = objReader["OP70结果"].ToString();
                productData.OP70暖芯正压值 = objReader["OP70暖芯正压值"].ToString();
                productData.OP70暖芯负压值 = objReader["OP70暖芯负压值"].ToString();
                productData.OP70蒸发器负压值 = objReader["OP70蒸发器负压值"].ToString();
                productData.托盘号 = objReader["托盘号"].ToString();
                productData.OP70操作时间 = objReader["OP70操作时间"].ToString();
                objProductData.Add(productData);
            }
            this.dataGridView1.AutoGenerateColumns = false;
            this.dataGridView1.DataSource = objProductData;
            objReader.Close();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            //创建Excel工作簿和工作表
            XSSFWorkbook workbook = new XSSFWorkbook();
            ISheet newsheet = workbook.CreateSheet("追溯记录");

            //创建Excel工作表的列标题
            IRow rowHeader = newsheet.CreateRow(0);
            rowHeader.CreateCell(0, CellType.String).SetCellValue("产品下线时间");
            rowHeader.CreateCell(1, CellType.String).SetCellValue(" 产品型号");
            rowHeader.CreateCell(2, CellType.String).SetCellValue(" 总成码");
            rowHeader.CreateCell(3, CellType.String).SetCellValue("内部码");
            //rowHeader.CreateCell(3, CellType.String).SetCellValue("ID");
            rowHeader.CreateCell(4, CellType.String).SetCellValue("托盘号");
            rowHeader.CreateCell(5, CellType.String).SetCellValue("OP70操作时间");
            rowHeader.CreateCell(6, CellType.String).SetCellValue("总结果");
            rowHeader.CreateCell(7, CellType.String).SetCellValue(" OP70结果");
            rowHeader.CreateCell(8, CellType.String).SetCellValue(" OP70暖芯正压值");
            rowHeader.CreateCell(9, CellType.String).SetCellValue(" OP70暖芯负压值");
            rowHeader.CreateCell(10, CellType.String).SetCellValue(" OP70蒸发器负压值");

            //循环创建Excel行，并填充数据
            for (int i = 0; i < objProductData.Count; i++)
            {
                IRow row = newsheet.CreateRow(i + 1);
                row.CreateCell(0, CellType.String).SetCellValue(objProductData[i].产品下线时间.ToString());
                row.CreateCell(1, CellType.String).SetCellValue(objProductData[i].产品型号);
                row.CreateCell(2, CellType.String).SetCellValue(objProductData[i].总成码);
                //row.CreateCell(3, CellType.String).SetCellValue(objProductData[i].ID);
                row.CreateCell(3, CellType.String).SetCellValue(objProductData[i].内部码);
                row.CreateCell(4, CellType.String).SetCellValue(objProductData[i].托盘号);
                row.CreateCell(5, CellType.String).SetCellValue(objProductData[i].OP70操作时间);
                row.CreateCell(6, CellType.String).SetCellValue(objProductData[i].总结果);
                row.CreateCell(7, CellType.String).SetCellValue(objProductData[i].OP70结果);
                row.CreateCell(8, CellType.String).SetCellValue(objProductData[i].OP70暖芯正压值);
                row.CreateCell(9, CellType.String).SetCellValue(objProductData[i].OP70暖芯负压值);
                row.CreateCell(10, CellType.String).SetCellValue(objProductData[i].OP70蒸发器负压值);

            }

            //选择文件位置
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.InitialDirectory = @"C:\Users\SpringRain\Desktop";
            sfd.Title = "请选择要保存的文件";
            sfd.Filter = "Excel表格|*.xlsx";
            sfd.ShowDialog(this);
            string path = sfd.FileName;

            //将输出流写入Excel工作簿
            try
            {
                using (FileStream fSwrite = File.OpenWrite(path))
                {
                    workbook.Write(fSwrite);
                    MessageBox.Show("保存成功");
                }
            }
            catch
            {

            }
        }
    }
}
